package per.chenxin.tools.foundation;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;

public class DBTable {

    static private final String STR_TABLE_CAT = "TABLE_CAT";

    static private final String STR_TABLE_SCHEM = "TABLE_SCHEM";

    static private final String STR_TABLE_NAME = "TABLE_NAME";

    static private final String STR_COLUMN_NAME = "COLUMN_NAME";

    static public final String STR_DATA_TYPE = "DATA_TYPE";

    static public final String STR_PKCOLUMN_NAME = "PKCOLUMN_NAME";

    Map<String, Map<String, Map<String, Object>>> mapColumnItem;

    Map<String, Map<String, Map<String, Object>>> mapColumnImportedKey;

    Map<String, Map<String, Object>> mapTable;

    Set<String> setCatalog;

    Set<String> setSchema;

    DatabaseMetaData databaseMetaData;

    String strBestRowIdentifier;

    public DBTable(DatabaseMetaData databaseMetaData) throws SQLException, SecurityException,
        IllegalArgumentException, NoSuchMethodException, IllegalAccessException,
        InvocationTargetException {

        this.databaseMetaData = databaseMetaData;
        ResultSet resultSet = null;
        mapColumnItem = Collections.synchronizedMap(new HashMap<String, Map<String, Map<String, Object>>>());
        mapColumnImportedKey = Collections.synchronizedMap(new HashMap<String, Map<String, Map<String, Object>>>());
        mapTable = Collections.synchronizedMap(new HashMap<String, Map<String, Object>>());
        setCatalog = Collections.synchronizedSet(new HashSet<String>());
        setSchema = Collections.synchronizedSet(new HashSet<String>());
        try {

            resultSet = DBUtil.getDatabaseMetaDataInfo(databaseMetaData,
                DBUtil.INT_BEST_ROW_IDENTIFIER);
            strBestRowIdentifier = DBUtil.getFirstRowString(resultSet, DBUtil.COLUMN_NAME);

            resultSet = DBUtil.getDatabaseMetaDataInfo(databaseMetaData, DBUtil.INT_GET_TABLES);
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

            while (resultSet.next()) {
                Map<String, Object> mapTableItem = new HashMap<String, Object>();
                for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                    Object obj = resultSet.getObject(i + 1);
                    if (obj != null)
                        mapTableItem.put(resultSetMetaData.getColumnName(i + 1), obj);

                }
                String strCatalog = resultSet.getString(STR_TABLE_CAT);
                if (strCatalog == null)
                    strCatalog = "";
                setCatalog.add(strCatalog);

                String strSchema = resultSet.getString(STR_TABLE_SCHEM);
                if (strSchema == null)
                    strSchema = "";
                setSchema.add(strSchema);

                mapTable.put(resultSet.getString(STR_TABLE_NAME), mapTableItem);
            }

            resultSet.close();

        } finally {
            if (resultSet != null)
                resultSet.close();
        }

    }

    public DBTable(Connection conn) throws SQLException, SecurityException,
        IllegalArgumentException, NoSuchMethodException, IllegalAccessException,
        InvocationTargetException {

        this(conn.getMetaData());
    }

    public Set<String> getSetCatalog() {

        return setCatalog;
    }

    public Set<String> getSetSchema() {

        return setSchema;
    }

    public Map<String, Map<String, Object>> getMapTable() {

        return mapTable;
    }

    public Map<String, Map<String, Object>> getMapTable(String strCatalog, String strSchema,
        String strFilter) {

        boolean isCatalogNull = (strCatalog == null || strCatalog.trim().length() == 0);
        boolean isSchemaNull = (strSchema == null || strSchema.trim().length() == 0);
        boolean isFilterNull = (strFilter == null || strFilter.trim().length() == 0);
        Pattern pattern = null;

        if (!isFilterNull)
            pattern = Pattern.compile(strFilter);

        Map<String, Map<String, Object>> mapTable = getMapTable();
        Map<String, Map<String, Object>> map = new HashMap<String, Map<String, Object>>();

        String[] objs = mapTable.keySet().toArray(new String[] {});
        for (int i = 0; i < objs.length; i++) {
            Map<String, Object> mapItem = mapTable.get(objs[i]);
            boolean isCatalogOK = (isCatalogNull || strCatalog.equalsIgnoreCase((String) mapItem.get(STR_TABLE_CAT)));
            boolean isSchemaOK = (isSchemaNull || strSchema.equalsIgnoreCase((String) mapItem.get(STR_TABLE_SCHEM)));
            boolean isFilterOK = (isFilterNull || pattern.matcher((String) objs[i]).matches());

            if (isCatalogNull || strSchema.equalsIgnoreCase((String) mapItem.get(STR_TABLE_CAT)))
                isCatalogOK = true;
            if (isCatalogOK && isSchemaOK && isFilterOK)
                map.put(objs[i], mapItem);

        }
        return map;
    }

    public String getSchemaFromTable(String strTablename) {

        if (!mapTable.containsKey(strTablename))
            return "";
        return (String) (mapTable.get(strTablename)).get(STR_TABLE_SCHEM);
    }

    //column info
    public Map<String, Object> getColumnItem(String strTable, String strColumn) {

        Map<String, Map<String, Object>> mapTable = mapColumnItem.get(strTable);
        if (mapTable == null) {
            try {
                mapTable = getMapTableColumnItem(strTable);
                if (mapTable == null)
                    return null;
                mapColumnItem.put(strTable, mapTable);
            } catch (SQLException e) {
                return null;
            }
        }

        return mapColumnItem.get(strTable).get(strColumn);
    }

    public Set<String> getColumnItemKeySet(String strTable) {

        Map<String, Map<String, Object>> mapTable = mapColumnItem.get(strTable);
        if (mapTable == null) {
            try {
                mapTable = getMapTableColumnItem(strTable);
                if (mapTable == null)
                    return null;
                mapColumnItem.put(strTable, mapTable);
            } catch (SQLException e) {
                return null;
            }
        }
        return mapColumnItem.get(strTable).keySet();
    }

    public Map<String, Map<String, Object>> getMapTableColumnItem(String table) throws SQLException {

        Map<String, Map<String, Object>> mapTableColumnItem = new HashMap<String, Map<String, Object>>();
        ResultSet resultSet = null;
        try {
            resultSet = databaseMetaData.getColumns(null, null, table, null);
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            while (resultSet.next()) {
                Map<String, Object> mapColumnItem = new HashMap<String, Object>();
                for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                    Object obj = resultSet.getString(i + 1);
                    if (obj != null)
                        mapColumnItem.put(resultSetMetaData.getColumnName(i + 1), obj);
                }
                mapTableColumnItem.put(resultSet.getString(STR_COLUMN_NAME), mapColumnItem);
            }
        } finally {
            DBUtil.closeResultSet(resultSet);
        }

        return mapTableColumnItem;
    }

    //key info
    public Map<String, Object> getColumnImportedKey(String strTable, String strColumn) {

        Map<String, Map<String, Object>> map = mapColumnImportedKey.get(strTable);
        if (map == null) {
            try {
                map = getMapTableColumnItem(strTable);
                if (map == null)
                    return null;
                mapColumnImportedKey.put(strTable, map);
            } catch (SQLException e) {
                return null;
            }
        }

        return mapColumnImportedKey.get(strTable).get(strColumn);
    }

    public Set<String> getColumnImportedKeyKeySet(String strTable) {

        Map<String, Map<String, Object>> mapTable = mapColumnImportedKey.get(strTable);
        if (mapTable == null) {
            try {
                mapTable = getMapTableImportedKey(strTable);
                if (mapTable == null)
                    return null;
                mapColumnImportedKey.put(strTable, mapTable);
            } catch (SQLException e) {
                return null;
            }
        }
        return mapColumnImportedKey.get(strTable).keySet();
    }

    public Map<String, Map<String, Object>> getMapTableImportedKey(String table)
        throws SQLException {

        Map<String, Map<String, Object>> mapTableImportedKeys = new HashMap<String, Map<String, Object>>();
        ResultSet resultSet = null;
        try {
            resultSet = databaseMetaData.getExportedKeys(null, null, table);
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            while (resultSet.next()) {
                Map<String, Object> mapImportedKeys = new HashMap<String, Object>();
                for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                    Object obj = resultSet.getString(i + 1);
                    if (obj != null)
                        mapImportedKeys.put(resultSetMetaData.getColumnName(i + 1), obj);
                }
                mapTableImportedKeys.put(resultSet.getString(STR_PKCOLUMN_NAME), mapImportedKeys);
            }
        } finally {
            DBUtil.closeResultSet(resultSet);
        }

        return mapTableImportedKeys;
    }

}
